<?php

class Groups
{
	public $pdo;
	
	function get_members($group)
	{
		$stmt = $this->pdo->prepare("SELECT USERS.NAME, USERS.ID
			                   FROM USERS, REGISTRATIONS
			                   WHERE REGISTRATIONS.GROUPS = ?
			                     AND USERS.ID = REGISTRATIONS.STUDENT
			                   ORDER BY USERS.NAME ASC");
		$stmt->execute(array($group));
		return $stmt->fetchAll(PDO::FETCH_CLASS);
	}
	
	public function get_potential_supervisers($instance)
	{
		$stmt = $this->pdo->prepare("SELECT ID, NAME
			                         FROM USERS
			                         WHERE ID NOT IN (SELECT REGISTRATIONS.STUDENT
			                                          FROM GROUPS, REGISTRATIONS
			                                          WHERE GROUPS.INSTANCE = ?
			                                            AND REGISTRATIONS.GROUPS = GROUPS.ID)
			                         ORDER BY NAME ASC");
		$stmt->execute(array($instance));
		return $stmt->fetchAll(PDO::FETCH_CLASS);
	}

	public function get_by_instance($instance)
	{
		$stmt = $this->pdo->prepare("SELECT GROUPS.ID, COALESCE(USERS.NAME,'Ei valvojaa') AS SUPERVISER_NAME, USERS.ID AS SUPERVISER_ID
			                         FROM (SELECT * FROM GROUPS WHERE INSTANCE = ?) AS GROUPS
			                         LEFT OUTER JOIN USERS ON (USERS.ID = GROUPS.TEACHER)
			                         ORDER BY GROUPS.ID ASC");
		$stmt->execute(array($instance));
		return $stmt->fetchAll(PDO::FETCH_CLASS);
	}

	public function get_by_superviser($user)
	{
		$stmt = $this->pdo->prepare("SELECT row_number() OVER (PARTITION BY COURSES.NAME), COURSES.NAME AS COURSE, GROUPS.ID
			                         FROM COURSES, INSTANCES, GROUPS
			                         WHERE GROUPS.TEACHER = ?
			                           AND INSTANCES.ID = GROUPS.INSTANCE
			                           AND COURSES.ID = INSTANCES.COURSE
			                         ORDER BY COURSES.NAME, INSTANCES.ID ASC");
		$stmt->execute(array($user));
		return $stmt->fetchAll(PDO::FETCH_CLASS);
	}
	
	public function insert($instance,$teacher)
	{
		if(strlen($teacher) == 0)
			$teacher = null;
	
		$stmt = $this->pdo->prepare("INSERT INTO GROUPS (INSTANCE, TEACHER) VALUES (?,?)");
		return $stmt->execute(array($instance,$teacher));
	}

	public function update($id,$teacher)
	{
		if(strlen($teacher) == 0)
			$teacher = null;
	
		$stmt = $this->pdo->prepare("UPDATE GROUPS SET TEACHER = ? WHERE ID = ?");
		return $stmt->execute(array($teacher,$id));
	}

	public function delete($id)
	{
		$stmt = $this->pdo->prepare("DELETE FROM GROUPS WHERE ID = ?");
		return $stmt->execute(array($id));
	}
}

?>
